package com.hw.shortlink.project.dao.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hw.shortlink.project.dao.entity.LinkAccessLogs;
import com.hw.shortlink.project.dao.entity.LinkAccessStats;
import com.hw.shortlink.project.dto.req.ShortLinkGroupStatsReqDto;
import com.hw.shortlink.project.dto.req.ShortLinkStatsReqDto;
import com.hw.shortlink.project.dto.resp.ShortLinkStatsTopIpRespDto;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public interface LinkAccessLogsMapper extends BaseMapper<LinkAccessLogs> {


    /**
     * 根据短链接获取指定日期内高频访问ip
     *
     * @param shortLinkStatsReqDto
     * @return
     */
    @Select("SELECT " +
            "    ip, " +
            "    COUNT(ip) AS cnt " +
            "FROM " +
            "    tb_link_access_logs " +
            "WHERE " +
            "    full_short_url = #{reqDto.fullShortUrl} " +
            "    AND gid = #{reqDto.gid} " +
            "    AND create_time BETWEEN #{reqDto.startDate} and CONCAT(DATE_FORMAT(#{reqDto.endDate}, '%Y-%m-%d'), ' 23:59:59') " +
            "GROUP BY " +
            "    ip " +
            "ORDER BY " +
            "    cnt DESC " +
            "LIMIT 5;")
    List<ShortLinkStatsTopIpRespDto> listTopIpByShortLink(@Param("reqDto") ShortLinkStatsReqDto shortLinkStatsReqDto);

    /**
     * 根据分组获取指定日期内高频访问ip
     *
     * @param shortLinkGroupStatsReqDto
     * @return
     */
    @Select("SELECT " +
            "    ip, " +
            "    COUNT(ip) AS cnt " +
            "FROM " +
            "    tb_link_access_logs " +
            "WHERE " +
            "    gid = #{reqDto.gid} " +
            "    AND create_time BETWEEN #{reqDto.startDate} and CONCAT(DATE_FORMAT(#{reqDto.endDate}, '%Y-%m-%d'), ' 23:59:59') " +
            "GROUP BY " +
            "    ip " +
            "ORDER BY " +
            "    cnt DESC " +
            "LIMIT 5;")
    List<ShortLinkStatsTopIpRespDto> listTopIpByGroup(@Param("reqDto") ShortLinkGroupStatsReqDto shortLinkGroupStatsReqDto);

    /**
     * 根据短链接获取指定日期内总的PV、UV、UIP数据
     */
    @Select("SELECT " +
            "    COUNT(user) AS pv, " +
            "    COUNT(DISTINCT user) AS uv, " +
            "    COUNT(DISTINCT ip) AS uip " +
            "FROM " +
            "    tb_link_access_logs " +
            "WHERE " +
            "    full_short_url = #{reqDto.fullShortUrl} " +
            "    AND gid = #{reqDto.gid} " +
            "    AND create_time BETWEEN #{reqDto.startDate} and CONCAT(DATE_FORMAT(#{reqDto.endDate}, '%Y-%m-%d'), ' 23:59:59') "
    )
    LinkAccessStats findPvUvUidStatsByShortLink(@Param("reqDto") ShortLinkStatsReqDto shortLinkStatsReqDto);

    /**
     * 根据分组获取指定日期内总的PV、UV、UIP数据
     */
    @Select("SELECT " +
            "    COUNT(user) AS pv, " +
            "    COUNT(DISTINCT user) AS uv, " +
            "    COUNT(DISTINCT ip) AS uip " +
            "FROM " +
            "    tb_link_access_logs " +
            "WHERE " +
            "    gid = #{reqDto.gid} " +
            "    AND create_time BETWEEN #{reqDto.startDate} and CONCAT(DATE_FORMAT(#{reqDto.endDate}, '%Y-%m-%d'), ' 23:59:59') "
    )
    LinkAccessStats findPvUvUidStatsByGroup(@Param("reqDto") ShortLinkGroupStatsReqDto shortLinkGroupStatsReqDto);


    /**
     * 根据短链接获取指定日期内新旧访客数据
     */
    @Select("SELECT " +
            "    SUM(old_user) AS oldUserCnt, " +
            "    SUM(new_user) AS newUserCnt " +
            "FROM ( " +
            "    SELECT " +
            "        CASE WHEN COUNT(DISTINCT DATE(create_time)) > 1 THEN 1 ELSE 0 END AS old_user, " +
            "        CASE WHEN COUNT(DISTINCT DATE(create_time)) = 1 " +
            "           AND MAX(create_time) >= #{reqDto.startDate} " +
            "           AND MAX(create_time) <= CONCAT(DATE_FORMAT(#{reqDto.endDate}, '%Y-%m-%d'), ' 23:59:59') THEN 1 ELSE 0 END AS new_user " +
            "    FROM " +
            "        tb_link_access_logs " +
            "    WHERE " +
            "        full_short_url = #{reqDto.fullShortUrl} " +
            "        AND gid = #{reqDto.gid} " +
            "    GROUP BY " +
            "        user " +
            ") AS user_counts;")
    HashMap<String, Object> findUvTypeCntByShortLink(@Param("reqDto") ShortLinkStatsReqDto shortLinkStatsReqDto);

    /**
     * 根据分组获取指定日期内新旧访客数据
     */
    @Select("SELECT " +
            "    SUM(old_user) AS oldUserCnt, " +
            "    SUM(new_user) AS newUserCnt " +
            "FROM ( " +
            "    SELECT " +
            "        CASE WHEN COUNT(DISTINCT DATE(create_time)) > 1 THEN 1 ELSE 0 END AS old_user, " +
            "        CASE WHEN COUNT(DISTINCT DATE(create_time)) = 1 " +
            "           AND MAX(create_time) >= #{reqDto.startDate} " +
            "           AND MAX(create_time) <= CONCAT(DATE_FORMAT(#{reqDto.endDate}, '%Y-%m-%d'), ' 23:59:59') THEN 1 ELSE 0 END AS new_user " +
            "    FROM " +
            "        tb_link_access_logs " +
            "    WHERE " +
            "   gid = #{reqDto.gid} " +
            "    GROUP BY " +
            "        user " +
            ") AS user_counts;")
    HashMap<String, Object> findUvTypeCntByGroup(@Param("reqDto") ShortLinkGroupStatsReqDto shortLinkGroupStatsReqDto);


    /**
     * 通过用户uv查询和短链接信息查询是否是新用户
     * @param users
     * @param gid
     * @param fullShortUrl
     * @param startDate
     * @param endDate
     * @return
     */
    List<Map<String, Object>> listUvTypeByUsers(@Param("users") List<String> users,
                                                @Param("gid") String gid, @Param("fullShortUrl") String fullShortUrl,
                                                @Param("startDate") Date startDate,@Param("endDate") Date endDate);






}


